package com.xinyirun.scm.core.system.mapper.master.inventory.v2;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinyirun.scm.bean.entity.master.inventory.MInventoryAccountEntity;
import com.xinyirun.scm.bean.entity.master.inventory.v2.MInventoryAccountV2Entity;
import com.xinyirun.scm.bean.system.vo.master.inventory.MInventoryAccountExportVo;
import com.xinyirun.scm.bean.system.vo.master.inventory.MInventoryAccountVo;
import com.xinyirun.scm.common.constant.DictConstant;
import com.xinyirun.scm.common.constant.SystemConstants;
import com.xinyirun.scm.common.serialtype.SerialType;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author htt
 * @since 2021-09-23
 */
@Repository
public interface MInventoryAccountV2Mapper extends BaseMapper<MInventoryAccountV2Entity> {

    /**
     * 页面查询列表
     */
    @Select("   <script>     "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "			select                                                                                                                                                                                                              "
            + "				t2.code business_code,                                                                                                                                                                                          "
            + "				t1.id ,                                                                                                                                                                                                         "
            + "				t2.order_no,                                                                                                                                                                                                    "
            + "				t2.contract_no,                                                                                                                                                                                                 "
            + "				t3.short_name owner_name,                                                                                                                        																"
            + "				t4.code sku_code,                                                                                                                                																"
            + "				t8.name business_name,                                                                                                                           																"
            + "				t7.name industry_name,                                                                                                                           																"
            + "				t6.name category_name,                                                                                                                           																"
            + "				t5.name goods_name,                                                                                                                              																"
            + "				t4.pm,                                                                                                                                           																"
            + "				t4.spec,                                                                                                                                         																"
            + "				t2.serial_type_name,                                                                                                                             																"
            + "				t9.name warehouse_name,                                                                                                                          																"
            + "				t1.qty,                                                                                                                                          																"
            + "				'"+ SystemConstants.INVENTORY_UNIT+"' unit,                                                                                                                                          							"
            + "				t1.qty_inventory + t1.qty_lock_inventory as qty_inventory_total,                                                                                                                                				"
            + "				t1.qty_inventory,                                                                                                                                																"
            + "				t1.qty_lock_inventory,                                                                                                                                														    "
            + "				t10.name operator,                                                                                                                               																"
            + "				t1.u_time,                                                                                                                                        																"
            + "				t12.label warehouse_type_name,                                                                                                                                        											"
            + "				t11.label  business_type_name                                                                                                                                      											    "
            + "			from m_inventory_account t1 left join (                                                                                                              																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_IN +"' as serial_type, '"+ SerialType.BILL_BUSINESS_IN_NAME +"' as serial_type_name, tt1.u_time,               "
            + "                 tt1.u_id from b_in  tt1 left join b_in_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                            "
            + "				union all                                                                                                                                        																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_OUT +"' as serial_type, '"+ SerialType.BILL_BUSINESS_OUT_NAME +"' as serial_type_name, tt1.u_time,             "
            + "                 tt1.u_id from b_out tt1 left join b_out_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                           "
            + "				union all                                                                                                                                        																"
            + "				select '' order_no, '' contract_no,tt2.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_ADJUST +"' as serial_type, '"+ SerialType.BILL_BUSINESS_ADJUST_NAME +"' as serial_type_name, tt1.u_time, tt1.u_id from b_adjust  tt1 left join b_adjust_detail tt2 on tt1.id = tt2.adjust_id      "
            + "			) t2 on t1.serial_id = t2.id and t1.serial_type = t2.serial_type                                                                                     																"
            + "			LEFT JOIN m_owner t3 on t1.owner_id = t3.id                                                                                                       																    "
            + "			LEFT JOIN m_goods_spec t4 on t1.sku_id = t4.id                                                                                                       																"
            + "			LEFT JOIN m_goods t5 on t4.goods_id = t5.id                                                                                                          																"
            + "			LEFT JOIN m_category t6 on t5.category_id = t6.id                                                                                                    																"
            + "			LEFT JOIN m_industry t7 on t6.industry_id = t7.id                                                                                                    																"
            + "			LEFT JOIN m_business_type t8 on t7.business_id = t8.id                                                                                               																"
            + "			LEFT JOIN m_warehouse t9 on t1.warehouse_id = t9.id                                                                                                  																"
            + "			LEFT JOIN m_staff t10 on t2.u_id = t10.id                                                                                                            																"
            + "   	    LEFT JOIN v_dict_info t11 on t11.code = '" + DictConstant.DICT_B_INVENTORY_ACCOUNT_TYPE + "' and t11.dict_value = t1.business_type                                                                                  "
            + "   	    LEFT JOIN v_dict_info t12 on t12.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE + "' and t12.dict_value = t9.warehouse_type                                                                                         "
            + "         WHERE   true                                                                                                                                                                                                        "
            + "            and (t1.owner_id =  #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                                                  "
            + "            and (t2.code like CONCAT ('%',#{p1.business_code,jdbcType=VARCHAR},'%') or #{p1.business_code,jdbcType=VARCHAR} is null or #{p1.business_code,jdbcType=VARCHAR} = '')                                            "
            + "            and (t2.order_no like CONCAT ('%',#{p1.order_no,jdbcType=VARCHAR},'%') or #{p1.order_no,jdbcType=VARCHAR} is null or #{p1.order_no,jdbcType=VARCHAR} = '')                                                       "
            + "            and (t2.contract_no like CONCAT ('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null or #{p1.contract_no,jdbcType=VARCHAR} = '')                                           "
            + "            and (concat(t5.name,t5.code, t4.name, t4.code) LIKE CONCAT ('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null or #{p1.goods_name,jdbcType=VARCHAR} = '')                   "
            + "            and (t6.name LIKE CONCAT ('%',#{p1.category_name,jdbcType=VARCHAR},'%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')                                            "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                                 "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)                                                                   "
            + "            and (t2.serial_type = #{p1.serial_type,jdbcType=VARCHAR} or #{p1.serial_type,jdbcType=VARCHAR} is null or #{p1.serial_type,jdbcType=VARCHAR} = '')                                                               "
//            + "            and (t1.business_type = #{p1.business_type,jdbcType=VARCHAR} or #{p1.business_type,jdbcType=VARCHAR} is null or #{p1.business_type,jdbcType=VARCHAR} = '')                                                               "
            + "            <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                                             "
            + "             and t1.warehouse_id in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.owner_ids != null and p1.owner_ids.length!=0' >                                                                                                                                                     "
            + "             and t1.owner_id in                                                                                                                                                                                              "
            + "                 <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                              "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.warehouse_types != null and p1.warehouse_types.length!=0' >                                                                                                                                         "
            + "             and t9.warehouse_type in                                                                                                                                                                                        "
            + "                 <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                        "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.business_types != null and p1.business_types.length!=0' >                                                                                                                                         "
            + "             and t1.business_type in                                                                                                                                                                                        "
            + "                 <foreach collection='p1.business_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                        "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                                                                        "
            + "    </script>  ")
    IPage<MInventoryAccountVo> selectPage(Page page, @Param("p1") MInventoryAccountVo searchCondition);


    /**
     * 页面查询列表
     */
    @Select("   <script>     "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "			select                                                                                                                                                                                                              "
            + "				count(1) c                                                                                                                                      											                    "
            + "			from m_inventory_account t1 left join (                                                                                                              																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_IN +"' as serial_type, '"+ SerialType.BILL_BUSINESS_IN_NAME +"' as serial_type_name, tt1.u_time,               "
            + "                 tt1.u_id from b_in  tt1 left join b_in_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                            "
            + "				union all                                                                                                                                        																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_OUT +"' as serial_type, '"+ SerialType.BILL_BUSINESS_OUT_NAME +"' as serial_type_name, tt1.u_time,             "
            + "                 tt1.u_id from b_out tt1 left join b_out_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                           "
            + "				union all                                                                                                                                        																"
            + "				select '' order_no, '' contract_no,id,code, owner_id, '"+ SerialType.BILL_BUSINESS_ADJUST +"' as serial_type, '"+ SerialType.BILL_BUSINESS_ADJUST_NAME +"' as serial_type_name, u_time, u_id from b_adjust      "
            + "			) t2 on t1.serial_id = t2.id and t1.serial_type = t2.serial_type                                                                                     																"
            + "			LEFT JOIN m_owner t3 on t1.owner_id = t3.id                                                                                                       																    "
            + "			LEFT JOIN m_goods_spec t4 on t1.sku_id = t4.id                                                                                                       																"
            + "			LEFT JOIN m_goods t5 on t4.goods_id = t5.id                                                                                                          																"
            + "			LEFT JOIN m_category t6 on t5.category_id = t6.id                                                                                                    																"
            + "			LEFT JOIN m_warehouse t9 on t1.warehouse_id = t9.id                                                                                                  																"
            + "         WHERE   true                                                                                                                                                                                                        "
            + "            and (t1.owner_id =  #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                                                  "
            + "            and (t2.code like CONCAT ('%',#{p1.business_code,jdbcType=VARCHAR},'%') or #{p1.business_code,jdbcType=VARCHAR} is null or #{p1.business_code,jdbcType=VARCHAR} = '')                                            "
            + "            and (t2.order_no like CONCAT ('%',#{p1.order_no,jdbcType=VARCHAR},'%') or #{p1.order_no,jdbcType=VARCHAR} is null or #{p1.order_no,jdbcType=VARCHAR} = '')                                                       "
            + "            and (t2.contract_no like CONCAT ('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null or #{p1.contract_no,jdbcType=VARCHAR} = '')                                           "
            + "            and (concat(t5.name,t5.code, t4.name, t4.code) LIKE CONCAT ('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null or #{p1.goods_name,jdbcType=VARCHAR} = '')                                                                          "
            + "            and (t6.name LIKE CONCAT ('%',#{p1.category_name,jdbcType=VARCHAR},'%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')                                                                          "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                                 "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)                                                                   "
            + "            and (t2.serial_type = #{p1.serial_type,jdbcType=VARCHAR} or #{p1.serial_type,jdbcType=VARCHAR} is null or #{p1.serial_type,jdbcType=VARCHAR} = '')                                                               "
//            + "            and (t1.business_type = #{p1.business_type,jdbcType=VARCHAR} or #{p1.business_type,jdbcType=VARCHAR} is null or #{p1.business_type,jdbcType=VARCHAR} = '')                                                               "
            + "            <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                                             "
            + "             and t1.warehouse_id in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.owner_ids != null and p1.owner_ids.length!=0' >                                                                                                                                             "
            + "             and t1.owner_id in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.warehouse_types != null and p1.warehouse_types.length!=0' >                                                                                                                                             "
            + "             and t9.warehouse_type in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.business_types != null and p1.business_types.length!=0' >                                                                                                                                         "
            + "             and t1.business_type in                                                                                                                                                                                        "
            + "                 <foreach collection='p1.business_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                        "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "

            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                                                                        "
            + "    </script>  ")
    Long selectPageMyCount(@Param("p1") MInventoryAccountVo searchCondition);

    @Select("   <script>     "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "			select                                                                                                                                                                                                              "
            + "				t2.code business_code,                                                                                                                                                                                          "
            + "             @row_num:= @row_num+ 1 as no,                                                                                                                                                                                   "
            + "				t2.order_no,                                                                                                                                                                                                    "
            + "				t2.contract_no,                                                                                                                                                                                                 "
            + "				t3.short_name owner_name,                                                                                                                        																"
            + "				t4.code sku_code,                                                                                                                                																"
            + "				t8.name business_name,                                                                                                                           																"
            + "				t7.name industry_name,                                                                                                                           																"
            + "				t6.name category_name,                                                                                                                           																"
            + "				t5.name goods_name,                                                                                                                              																"
            + "				t4.pm,                                                                                                                                           																"
            + "				t4.spec,                                                                                                                                         																"
            + "				t2.serial_type_name,                                                                                                                             																"
            + "				t9.name warehouse_name,                                                                                                                          																"
            + "				t1.qty,                                                                                                                                          																"
            + "				'"+ SystemConstants.INVENTORY_UNIT+"' unit,                                                                                                                                          							"
            + "				t1.qty_inventory + t1.qty_lock_inventory as qty_inventory_total,                                                                                                                                				"
            + "				t1.qty_inventory,                                                                                                                                																"
            + "				t1.qty_lock_inventory,                                                                                                                                														    "
            + "				t10.name operator,                                                                                                                               																"
            + "				t1.u_time,                                                                                                                                        																"
            + "				t12.label warehouse_type_name,                                                                                                                                        											"
            + "				t11.label  business_type_name                                                                                                                                      											    "
            + "			from m_inventory_account t1 left join (                                                                                                              																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_IN +"' as serial_type, '"+ SerialType.BILL_BUSINESS_IN_NAME +"' as serial_type_name, tt1.u_time,               "
            + "                 tt1.u_id from b_in  tt1 left join b_in_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                            "
            + "				union all                                                                                                                                        																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_OUT +"' as serial_type, '"+ SerialType.BILL_BUSINESS_OUT_NAME +"' as serial_type_name, tt1.u_time,             "
            + "                 tt1.u_id from b_out tt1 left join b_out_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                           "
            + "				union all                                                                                                                                        																"
            + "				select '' order_no, '' contract_no,tt2.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_ADJUST +"' as serial_type, '"+ SerialType.BILL_BUSINESS_ADJUST_NAME +"' as serial_type_name, tt1.u_time, tt1.u_id from b_adjust  tt1 left join b_adjust_detail tt2 on tt1.id = tt2.adjust_id      "
            + "			) t2 on t1.serial_id = t2.id and t1.serial_type = t2.serial_type                                                                                     																"
            + "			LEFT JOIN m_owner t3 on t1.owner_id = t3.id                                                                                                       																    "
            + "			LEFT JOIN m_goods_spec t4 on t1.sku_id = t4.id                                                                                                       																"
            + "			LEFT JOIN m_goods t5 on t4.goods_id = t5.id                                                                                                          																"
            + "			LEFT JOIN m_category t6 on t5.category_id = t6.id                                                                                                    																"
            + "			LEFT JOIN m_industry t7 on t6.industry_id = t7.id                                                                                                    																"
            + "			LEFT JOIN m_business_type t8 on t7.business_id = t8.id                                                                                               																"
            + "			LEFT JOIN m_warehouse t9 on t1.warehouse_id = t9.id                                                                                                  																"
            + "			LEFT JOIN m_staff t10 on t2.u_id = t10.id                                                                                                            																"
            + "   	    LEFT JOIN v_dict_info t11 on t11.code = '" + DictConstant.DICT_B_INVENTORY_ACCOUNT_TYPE + "' and t11.dict_value = t1.business_type                                                                                  "
            + "   	    LEFT JOIN v_dict_info t12 on t12.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE + "' and t12.dict_value = t9.warehouse_type                                                                                         "
            + "         ,(select @row_num:=0) t13                                                                                                                                                                                           "
            + "         WHERE   true                                                                                                                                                                                                        "
            + "            and (t1.owner_id =  #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                                                  "
            + "            and (t2.code like CONCAT ('%',#{p1.business_code,jdbcType=VARCHAR},'%') or #{p1.business_code,jdbcType=VARCHAR} is null or #{p1.business_code,jdbcType=VARCHAR} = '')                                            "
            + "            and (t2.order_no like CONCAT ('%',#{p1.order_no,jdbcType=VARCHAR},'%') or #{p1.order_no,jdbcType=VARCHAR} is null or #{p1.order_no,jdbcType=VARCHAR} = '')                                                       "
            + "            and (t2.contract_no like CONCAT ('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null or #{p1.contract_no,jdbcType=VARCHAR} = '')                                           "
            + "            and (concat(t5.name,t5.code, t4.name, t4.code) LIKE CONCAT ('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null or #{p1.goods_name,jdbcType=VARCHAR} = '')                                                                          "
            + "            and (t6.name LIKE CONCAT ('%',#{p1.category_name,jdbcType=VARCHAR},'%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')                                            "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                                 "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)                                                                   "
            + "            and (t2.serial_type = #{p1.serial_type,jdbcType=VARCHAR} or #{p1.serial_type,jdbcType=VARCHAR} is null or #{p1.serial_type,jdbcType=VARCHAR} = '')                                                               "
//            + "            and (t1.business_type = #{p1.business_type,jdbcType=VARCHAR} or #{p1.business_type,jdbcType=VARCHAR} is null or #{p1.business_type,jdbcType=VARCHAR} = '')                                                               "
            + "            <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                                             "
            + "             and t1.warehouse_id in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.owner_ids != null and p1.owner_ids.length!=0' >                                                                                                                                                     "
            + "             and t1.owner_id in                                                                                                                                                                                              "
            + "                 <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                              "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.warehouse_types != null and p1.warehouse_types.length!=0' >                                                                                                                                         "
            + "             and t9.warehouse_type in                                                                                                                                                                                        "
            + "                 <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                        "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.ids != null and p1.ids.length!=0' >                                                                                                                                                                 "
            + "             and t1.id in                                                                                                                                                                                                    "
            + "                 <foreach collection='p1.ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                                    "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.business_types != null and p1.business_types.length!=0' >                                                                                                                                         "
            + "             and t1.business_type in                                                                                                                                                                                        "
            + "                 <foreach collection='p1.business_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                        "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                                                                        "
            + "    </script>  ")
    List<MInventoryAccountExportVo> selectExportList(@Param("p1") MInventoryAccountVo searchCondition);

    @Select("   <script>     "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "			select                                                                                                                                                                                                              "
            + "				sum(t1.qty) qty                                                                                                                                      											                "
            + "			from m_inventory_account t1 left join (                                                                                                              																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_IN +"' as serial_type, '"+ SerialType.BILL_BUSINESS_IN_NAME +"' as serial_type_name, tt1.u_time,               "
            + "                 tt1.u_id from b_in  tt1 left join b_in_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                            "
            + "				union all                                                                                                                                        																"
            + "				select tt3.order_no, tt3.contract_no,tt1.id,tt1.code, tt1.owner_id, '"+ SerialType.BILL_BUSINESS_OUT +"' as serial_type, '"+ SerialType.BILL_BUSINESS_OUT_NAME +"' as serial_type_name, tt1.u_time,             "
            + "                 tt1.u_id from b_out tt1 left join b_out_plan_detail tt2 on tt1.plan_detail_id = tt2.id LEFT JOIN b_order tt3 on tt2.order_id = tt3.serial_id and tt2.order_type = tt3.serial_type                           "
            + "				union all                                                                                                                                        																"
            + "				select '' order_no, '' contract_no,id,code, owner_id, '"+ SerialType.BILL_BUSINESS_ADJUST +"' as serial_type, '"+ SerialType.BILL_BUSINESS_ADJUST_NAME +"' as serial_type_name, u_time, u_id from b_adjust      "
            + "			) t2 on t1.serial_id = t2.id and t1.serial_type = t2.serial_type                                                                                     																"
            + "			LEFT JOIN m_owner t3 on t1.owner_id = t3.id                                                                                                       																    "
            + "			LEFT JOIN m_goods_spec t4 on t1.sku_id = t4.id                                                                                                       																"
            + "			LEFT JOIN m_goods t5 on t4.goods_id = t5.id                                                                                                          																"
            + "			LEFT JOIN m_category t6 on t5.category_id = t6.id                                                                                                    																"
            + "			LEFT JOIN m_warehouse t9 on t1.warehouse_id = t9.id                                                                                                  																"
            + "         WHERE   true                                                                                                                                                                                                        "
            + "            and (t1.owner_id =  #{p1.owner_id,jdbcType=INTEGER} or #{p1.owner_id,jdbcType=INTEGER} is null)                                                                                                                  "
            + "            and (t2.code like CONCAT ('%',#{p1.business_code,jdbcType=VARCHAR},'%') or #{p1.business_code,jdbcType=VARCHAR} is null or #{p1.business_code,jdbcType=VARCHAR} = '')                                            "
            + "            and (t2.order_no like CONCAT ('%',#{p1.order_no,jdbcType=VARCHAR},'%') or #{p1.order_no,jdbcType=VARCHAR} is null or #{p1.order_no,jdbcType=VARCHAR} = '')                                                       "
            + "            and (t2.contract_no like CONCAT ('%',#{p1.contract_no,jdbcType=VARCHAR},'%') or #{p1.contract_no,jdbcType=VARCHAR} is null or #{p1.contract_no,jdbcType=VARCHAR} = '')                                           "
            + "            and (concat(t5.name,t5.code, t4.name, t4.code) LIKE CONCAT ('%',#{p1.goods_name,jdbcType=VARCHAR},'%') or #{p1.goods_name,jdbcType=VARCHAR} is null or #{p1.goods_name,jdbcType=VARCHAR} = '')                                                                          "
            + "            and (t6.name LIKE CONCAT ('%',#{p1.category_name,jdbcType=VARCHAR},'%') or #{p1.category_name,jdbcType=VARCHAR} is null or #{p1.category_name,jdbcType=VARCHAR} = '')                                                                          "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                                 "
            + "            AND (date_format(t1.u_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)                                                                   "
            + "            and (t2.serial_type = #{p1.serial_type,jdbcType=VARCHAR} or #{p1.serial_type,jdbcType=VARCHAR} is null or #{p1.serial_type,jdbcType=VARCHAR} = '')                                                               "
//            + "            and (t1.business_type = #{p1.business_type,jdbcType=VARCHAR} or #{p1.business_type,jdbcType=VARCHAR} is null or #{p1.business_type,jdbcType=VARCHAR} = '')                                                               "
            + "            <if test='p1.warehouse_ids != null and p1.warehouse_ids.length!=0' >                                                                                                                                             "
            + "             and t1.warehouse_id in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.owner_ids != null and p1.owner_ids.length!=0' >                                                                                                                                             "
            + "             and t1.owner_id in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.owner_ids' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.warehouse_types != null and p1.warehouse_types.length!=0' >                                                                                                                                             "
            + "             and t9.warehouse_type in                                                                                                                                                                                          "
            + "                 <foreach collection='p1.warehouse_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                          "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            + "            <if test='p1.business_types != null and p1.business_types.length!=0' >                                                                                                                                         "
            + "             and t1.business_type in                                                                                                                                                                                        "
            + "                 <foreach collection='p1.business_types' item='item' index='index' open='(' separator=',' close=')'>                                                                                                        "
            + "                  #{item}                                                                                                                                                                                                    "
            + "                 </foreach>                                                                                                                                                                                                  "
            + "            </if>                                                                                                                                                                                                            "
            // 仓库权限
            + "     ${p1.params.dataScopeAnnotation}                                                                                                                                                                                        "
            + "    </script>  ")
    MInventoryAccountVo selectListSum(@Param("p1") MInventoryAccountVo searchCondition);
}
